<HTML>
<HEAD>
<TITLE>
JavaScript Spreadsheet II: any size up to the max
</TITLE>
</HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
var sheetHeight = 7; // Number of rows in the spreadsheet (5-9)
var sheetWidth = 8; // Number of columns (5-26)
var cellArray;
var lastCellWithFocus = '';
var CellID = "~ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var digits = "0123456789.";
var formulas = false;
var base = '';
//--------------------------------------------------------------------------
// "Defines" the structure of a spreadsheet cell.
//--------------------------------------------------------------------------
function Cell(id)
{
this.formula = ''; // Default
this.value = '0'; // Default
this.empty = true; // Never had anything entered into it.
this.id = id; // A1, B2, etc.
this.current = false;
}
//--------------------------------------------------------------------------
// Determines whether the string argument represents a number. Exponential
// formats are NOT supported!
//--------------------------------------------------------------------------
function isANum(what)
{
if(what.indexOf('-') > 0)
return false;
for(var i = 0; i < what.length; i++)
if(digits.indexOf(what.charAt(i)) == -1)
return false;
return true;
}
//--------------------------------------------------------------------------
// Converts cell alpha reference ([A]1,[B]2,[C]3...) into array index.
//--------------------------------------------------------------------------
function toCellIndex(f)
{
f = f.toUpperCase();
var n=CellID.indexOf(f);
if(n == -1)
n = 1;
return n - 1;
}
//--------------------------------------------------------------------------
// Returns the value of a cell, or the value of the formula of the cell.
//--------------------------------------------------------------------------
function ValueOf(token)
{
var i = toCellIndex(token.charAt(0));
if(i < 0 || i > (sheetWidth - 1)) {
alert('Cell column reference of "'+token+'" outside spreadsheet!');
return 0;
}
var ch = token.charAt(1);
var j = parseInt(ch)-1;
if(j < 0 || j > (sheetHeight - 1)) {
alert('Cell row reference of "'+token+'" outside spreadsheet!');
return 0;
}
var c = cellArray[i][j];
if(c.formula != '')
//
// If the cell value doesn't reflect the formula, recalculate
//
if(!c.current) {
var res = Evaluate(c.formula);
cellArray[i][j].value = res;
cellArray[i][j].current = true;
return res;
}
return c.value;
}
//--------------------------------------------------------------------------
// Replaces tokens representing cell references with actual cell values
// and evaluates the whole calculation.
//--------------------------------------------------------------------------
function Evaluate(formula)
{
var result = 0;
if(formula.indexOf(base) == -1) {
var fstring = '';
var i = 1;
var l = formula.length;
while(i < l) {
var ch = formula.charAt(i);
var uch = ch.toUpperCase();
if(CellID.indexOf(uch) != -1) {
var ix = parseInt(formula.charAt(i+1));
if(ix >= 1 && ix <= sheetHeight) {
i++;
var token = uch + ix;
fstring += ValueOf(token);
} else
fstring += ch;
} else
fstring += ch;
i++;
}
result = eval(fstring);
} else
alert("A cell formula cannot reference its own cell, or\nbe referenced by another cell contained in the formula.\nFormula ["+formula+"] references base cell "+base);
return result;
}
//--------------------------------------------------------------------------
// Forces all formulas to be recalculated
//--------------------------------------------------------------------------
function MarkNonCurrent()
{
for(var i = 0; i < sheetWidth; i++)
for(var j = 0; j < sheetHeight; j++)
if(cellArray[i][j].formula != '')
cellArray[i][j].current = false;
}
//--------------------------------------------------------------------------
// Recalculates the spreadsheet based on a new value or formula for the
// cell indicated by 'who'
//--------------------------------------------------------------------------
function Recalc(who)
{
var i = toCellIndex(who.charAt(0));
var ch = who.charAt(1);
var j = parseInt(ch)-1;
var contents = eval('document.forms[0].'+who+'.value');
var c = contents.charAt(0);
if(c == '=') {
formulas = true;
cellArray[i][j].formula = contents;
}
else {
if(!isANum(contents)) {
alert("Cell contents must either be a number or a formula.");
eval('document.forms[0].'+who+'.value=0');
return;
}
cellArray[i][j].value = contents;
cellArray[i][j].formula = '';
}
base = who;
cellArray[i][j].empty = false;
if(formulas) {
MarkNonCurrent();
for(i = (sheetWidth - 1); i >= 0; i--)
for(j = (sheetHeight - 1); j >= 0; j--) {
var cell = cellArray[i][j];
if(cell.formula != '' && !cell.current) {
cellArray[i][j].value = Evaluate(cell.formula);
cellArray[i][j].current = true;
}
}
for(i = 0; i < sheetWidth; i++) {
for(j = 0; j < sheetHeight; j++) {
var cell = cellArray[i][j];
if(cell.formula != '') {
var evalString = 'document.forms[0].'+cell.id+'.value='+cell.value;
var m = eval(evalString);
}
}
}
}
}
//--------------------------------------------------------------------------
// Moves the contents of the spreadsheet cell in the form to the work array
//--------------------------------------------------------------------------
function gotFocus(who)
{
var i = toCellIndex(who.charAt(0));
var ch = who.charAt(1);
var j = parseInt(ch)-1;
var cell = cellArray[i][j];
if(cell.formula != '')
document.forms[0].Edit.value = cell.formula;
else
document.forms[0].Edit.value = cell.value;
lastCellWithFocus = who;
}
//--------------------------------------------------------------------------
// Takes contents of edit field and puts it back into the last accessed cell
//--------------------------------------------------------------------------
function onEdit()
{
if(lastCellWithFocus != '') {
var i = toCellIndex(lastCellWithFocus.charAt(0));
var ch = lastCellWithFocus.charAt(1);
var j = parseInt(ch)-1;
var contents = eval('document.forms[0].Edit.value');
var m;
var evalString;
if(contents == '')
contents = 0;
document.forms[0].Edit.value = contents;
if(contents.charAt(0) == '=') {
cellArray[i][j].formula=contents;
} else
cellArray[i][j].value=contents;
Recalc(lastCellWithFocus);
}
}
//--------------------------------------------------------------------------
// Outputs the HTML FORM data for a spreadsheet of the required size
//--------------------------------------------------------------------------
function DrawSheet()
{
var i;
var j;
document.writeln('<TABLE BGCOLOR="#A0A0FF" CELLPADDING=0 CELLSPACING=0>');
document.writeln('<TR>');
document.writeln('<TH BGCOLOR="#FFFFFF">+</TH>');
for(i = 1; i <= sheetWidth; i++)
document.write('<TH BGCOLOR="#A0FFA0">'+CellID.charAt(i)+'</TH>');
document.writeln('</TR>');
for(j = 0; j < sheetHeight; j++) {
document.writeln('<TR><TH BGCOLOR="#FFFFA0">'+(j+1)+'</TH>');
for(i = 0; i < sheetWidth; i++) {
var addr = cellArray[i][j].id;
document.writeln("<TD><INPUT NAME=\""+addr+"\" SIZE=9 ALIGN=\"LEFT\" VALUE=\"0\" onChange=\"Recalc('"+addr+"')\" onFocus=\"gotFocus('"+addr+"')\"></TD>");
}
document.writeln('</TR>');
}
document.writeln('</TABLE>');
}
//--------------------------------------------------------------------------
// Creates the spreadsheet and populates the cell ID's
//--------------------------------------------------------------------------
function Setup()
{
cellArray = new Array();
for(var n = 0; n < sheetWidth; n++)
cellArray[n] = new Array();
for(var i = 0; i < sheetWidth; i++)
for(var j = 0; j < sheetHeight; j++)
cellArray[i][j] = new Cell(CellID.charAt(i+1)+(j+1));
}
//-->
</SCRIPT>
.
.
.
</BODY>
</HTML>
Copyright ©1998 by Charles River Media, All Rights Reserved